
package banco_imobiliario.dao;

import banco_imobiliario.model.Imovel;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author alessandro
 */
public class ImovelDAOImpl implements ImovelDAO{

    private Connection conexaoBanco = null;
    private PreparedStatement preparedStatement = null;
    private ResultSet resultadoConsulta = null;
    
    @Override
    public void insertLogradouro(Imovel imovel) {
        
        String sqlQuery = "INSERT INTO logradouro (nome, aluguel, aluguel_uma_casa, " +
                          "aluguel_duas_casas, aluguel_tres_casas, " +
                          "aluguel_quatro_casas, aluguel_hotel, hotel, casa, hipoteca, valor) " +
                          "VALUES (?,?,?,?,?,?,?,?,?,?,?)"; 
       
		
        try {
            
            conexaoBanco = DAOConection.getConnection();
            preparedStatement = conexaoBanco.prepareStatement(sqlQuery);

            preparedStatement.setString(1, imovel.getNome());
            preparedStatement.setInt(2, imovel.getAluguel());
            preparedStatement.setInt(3, imovel.getAluguelUmaCasa());
            preparedStatement.setInt(4, imovel.getAluguelDuasCasas());
            preparedStatement.setInt(5, imovel.getAluguelTresCasas());
            preparedStatement.setInt(6, imovel.getAluguelQuatroCasas());
            preparedStatement.setInt(7, imovel.getAluguelHotel());
            preparedStatement.setInt(8, imovel.getPrecoConstrucaoHotel());
            preparedStatement.setInt(9, imovel.getPrecoConstrucaoResidencia());
            preparedStatement.setInt(10, (int)imovel.getHipoteca());
            preparedStatement.setInt(11, (int)imovel.getPreco());
            
            preparedStatement.execute();

            preparedStatement.close();
            conexaoBanco.close();

        } catch (SQLException e) {
                System.out.println("Erro ao inserir novo imóvel: " + e);
        }
        
        
    }

    @Override
    public void updateLogradouro(Imovel imovel) {
        
         String sqlQuery = "UPDATE logradouro SET nome = ?, aluguel = ?, " +
                           "aluguel_uma_casa = ?, aluguel_duas_casas = ?, " +
                           "aluguel_tres_casas = ?, aluguel_quatro_casas = ?, " +
                           "aluguel_hotel = ?, hotel = ?, casa = ?, hipoteca = ?, valor = ? WHERE id = ?"; 

        try {

            conexaoBanco = DAOConection.getConnection();
            preparedStatement = conexaoBanco.prepareStatement(sqlQuery);

            preparedStatement.setString(1, imovel.getNome());
            preparedStatement.setInt(2, imovel.getAluguel());
            preparedStatement.setInt(3, imovel.getAluguelUmaCasa());
            preparedStatement.setInt(4, imovel.getAluguelDuasCasas());
            preparedStatement.setInt(5, imovel.getAluguelTresCasas());
            preparedStatement.setInt(6, imovel.getAluguelQuatroCasas());
            preparedStatement.setInt(7, imovel.getAluguelHotel());
            preparedStatement.setInt(8, imovel.getPrecoConstrucaoHotel());
            preparedStatement.setInt(9, imovel.getPrecoConstrucaoResidencia());
            preparedStatement.setInt(10, (int)imovel.getHipoteca());
            preparedStatement.setInt(11, (int)imovel.getPreco());
            preparedStatement.setInt(12, imovel.getId());

            preparedStatement.executeUpdate();

            preparedStatement.close();
            conexaoBanco.close();

        } catch (SQLException e) {
                System.out.println("Erro ao atualizar imóvel: " + e);
        }        
        
    }

    @Override
    public void deleteLogradouro(int id) {
        
        String sqlQuery = "DELETE FROM logradouro WHERE id = ?"; 

        try {

            conexaoBanco = DAOConection.getConnection();
            preparedStatement = conexaoBanco.prepareStatement(sqlQuery);

            preparedStatement.setInt(1, id);
            preparedStatement.execute();

            preparedStatement.close();
            conexaoBanco.close();

        } catch (SQLException e) {
                System.out.println("Erro ao deletar o imóvel: " + e);
        }

        
    }

    @Override
    public ArrayList<Imovel> getAllLogradouros() {
        
        Imovel imovel = null;
        ArrayList<Imovel> imoveis = new ArrayList<Imovel>();
        
        String sqlQuery = "SELECT id, nome, aluguel, aluguel_uma_casa, aluguel_duas_casas, " +
                          "aluguel_tres_casas, aluguel_quatro_casas, aluguel_hotel, hotel, casa, hipoteca, valor, indice " +
                          "FROM logradouro";
        
        try{
           
            conexaoBanco = DAOConection.getConnection();
            preparedStatement = conexaoBanco.prepareStatement(sqlQuery);        
            resultadoConsulta = preparedStatement.executeQuery();
            
            while(resultadoConsulta.next()){
                
                imovel = new Imovel();
                
                imovel.setId( Integer.valueOf(resultadoConsulta.getString("id")) );
                imovel.setNome( resultadoConsulta.getString("nome") );
                imovel.setAluguel( Integer.valueOf(resultadoConsulta.getString("aluguel")) );
                imovel.setAluguelUmaCasa( Integer.valueOf(resultadoConsulta.getString("aluguel_uma_casa")) );
                imovel.setAluguelDuasCasas( Integer.valueOf(resultadoConsulta.getString("aluguel_duas_casas")) );
                imovel.setAluguelTresCasas( Integer.valueOf(resultadoConsulta.getString("aluguel_tres_casas")) );
                imovel.setAluguelQuatroCasas( Integer.valueOf(resultadoConsulta.getString("aluguel_quatro_casas")) );
                imovel.setAluguelHotel( Integer.valueOf(resultadoConsulta.getString("aluguel_hotel")) );
                imovel.setPrecoConstrucaoHotel( Integer.valueOf(resultadoConsulta.getString("hotel")) );
                imovel.setPrecoConstrucaoResidencia( Integer.valueOf(resultadoConsulta.getString("casa")) );
                imovel.setHipoteca(Integer.valueOf(resultadoConsulta.getString("hipoteca")) );
                imovel.setPreco(Integer.valueOf(resultadoConsulta.getString("valor")) );
                imovel.setIndice(Integer.valueOf(resultadoConsulta.getString("indice")) );
                
                imoveis.add(imovel);
            }     
            
            preparedStatement.close();
            conexaoBanco.close();
            
            
        }catch(SQLException e){
            System.out.println("Erro ao listar os imóveis: "+ e);
        }
       
        return imoveis;
    }

    @Override
    public Imovel buscarImovel(int id) {
         
        Imovel imovel = new Imovel();
               
        String sqlQuery = "SELECT id, nome, aluguel, aluguel_uma_casa, aluguel_duas_casas, " +
                          "aluguel_tres_casas, aluguel_quatro_casas, aluguel_hotel, hotel, casa, hipoteca, valor, indice " +
                          "FROM logradouro WHERE indice = ?";
        
        try{
           
            conexaoBanco = DAOConection.getConnection();
            preparedStatement = conexaoBanco.prepareStatement(sqlQuery);    
            preparedStatement.setInt(1, id);
            resultadoConsulta = preparedStatement.executeQuery();
            
            while(resultadoConsulta.next()){
                
               
                
                imovel.setId( Integer.valueOf(resultadoConsulta.getString("id")) );
                imovel.setNome( resultadoConsulta.getString("nome") );
                imovel.setAluguel( Integer.valueOf(resultadoConsulta.getString("aluguel")) );
                imovel.setAluguelUmaCasa( Integer.valueOf(resultadoConsulta.getString("aluguel_uma_casa")) );
                imovel.setAluguelDuasCasas( Integer.valueOf(resultadoConsulta.getString("aluguel_duas_casas")) );
                imovel.setAluguelTresCasas( Integer.valueOf(resultadoConsulta.getString("aluguel_tres_casas")) );
                imovel.setAluguelQuatroCasas( Integer.valueOf(resultadoConsulta.getString("aluguel_quatro_casas")) );
                imovel.setAluguelHotel( Integer.valueOf(resultadoConsulta.getString("aluguel_hotel")) );
                imovel.setPrecoConstrucaoHotel( Integer.valueOf(resultadoConsulta.getString("hotel")) );
                imovel.setPrecoConstrucaoResidencia( Integer.valueOf(resultadoConsulta.getString("casa")) );
                imovel.setHipoteca(Integer.valueOf(resultadoConsulta.getString("hipoteca")) );
                imovel.setPreco(Integer.valueOf(resultadoConsulta.getString("valor")) );
                imovel.setIndice(Integer.valueOf(resultadoConsulta.getString("indice")) );
                
                
            }     
            
            preparedStatement.close();
            conexaoBanco.close();
            
            
        }catch(SQLException e){
            System.out.println("Erro ao listar os imóveis: "+ e);
        }
       
        return imovel;
    }

    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
}
